Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# TRANSLATE

Syntax

Description of the illustration translate.gif

Purpose

`TRANSLATE` returns `expr` with all occurrences of each character in `from_string` replaced by its corresponding character in `to_string`. Characters in `expr` that are not in `from_string` are not replaced. If `expr` is a character string, then you must enclose it in single quotation marks. The argument `from_string` can contain more characters than `to_string`. In this case, the extra characters at the end of `from_string` have no corresponding characters in `to_string`. If these extra characters appear in `char`, then they are removed from the return value.

You cannot use an empty string for `to_string` to remove all characters in `from_string` from the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null.

`TRANSLATE` provides functionality related to that provided by the `REPLACE` function. `REPLACE` lets you substitute a single string for another single string, as well as remove character strings. `TRANSLATE` lets you make several single-character, one-to-one substitutions in one operation.

This function does not support `CLOB` data directly. However, `CLOB`s can be passed in as arguments through implicit data conversion.

See Also: "Datatype Comparison Rules" for more information and REPLACE

Examples

The following statement translates a book title into a string that could be used (for example) as a filename. The `from_string` contains four characters: a space, asterisk, slash, and apostrophe (with an extra apostrophe as the escape character). The `to_string` contains only three underscores. This leaves the fourth character in the `from_string` without a corresponding replacement, so apostrophes are dropped from the returned value.

    SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;

    TRANSLATE('SQL*PLUSU
    --------------------
    SQL_Plus_Users_Guide